package com.intelligentmeterreadingsys.waterlist.reviewdata.db;

import java.util.ArrayList;
import java.util.List;

import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.UserWaterVO;
import com.intelligentmeterreadingsys.povo.WaterInfoVO;
import com.intelligentmeterreadingsys.util.DateUtil;
import com.intelligentmeterreadingsys.util.Model;
import com.intelligentmeterreadingsys.util.SqlUtil;

import dbhelper.DbTableName;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

/**
 * 抄表数据审核
 * @author king
 *
 */
public class ReviewDataDao extends BaseDao{
     public ReviewDataDao(Context context) {
		super(context);
		this.db=super.getDb();
	}

	private SQLiteDatabase db;
	
	/**
	 * 查询一个复查信息
	 * @param RFID
	 * @param waterid
	 * @return
	 */
	public UserWaterVO queryOneReview(String RFID,String waterid){
		
		UserWaterVO water = null;
		String waternumber=null;
		String waternumberSql="select w.[waternumber] from waterinfo w where 0=0";
		if(RFID!=null){
			waternumberSql=waternumberSql+"  and w.[RFIDnumber]='"+RFID+"'";
		}
		if(waterid!=null){
			waternumberSql=waternumberSql+"  and w.[_id]="+waterid;
		}
		Cursor c=db.rawQuery(waternumberSql, null);
		if(c.moveToNext()){
			waternumber=c.getString(c.getColumnIndex("waternumber"));
		}
		if(c!=null){
			c.close();
		}
		if(waternumber==null||waternumber.equals("")){
			return null;
		}
		
		String sql = "select w.[_id] as waterid, w.[waternumber],w.[usernumber],w.[wateradds], w.[lastmonthwater],w.[thismonthwater]"
				+ " ,w.[isupload],w.[metercaliber],u.[username],u.[phone],w.[iscopystatus],w.[alarmlimit],w.[lowalarmlimit] ,w.[waterproperties] " +
				" from waterinfo w,userinfo u where w.[waternumber]='"+waternumber +				
				 "' and w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='1' and u.[ismultiplecopy]='1'" ;
				
		
		System.out.println(sql);

		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			water = new UserWaterVO();			
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setLastmonthwater(cursor.getDouble(cursor
					.getColumnIndex("lastmonthwater")));
			water.setMetercaliber(cursor.getString(cursor
					.getColumnIndex("metercaliber")));
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor
					.getColumnIndex("usernumber")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			water.setAlarmlimit(cursor.getDouble(cursor
					.getColumnIndex("alarmlimit")));
			water.setLowalarmlimit(cursor.getDouble(cursor
					.getColumnIndex("lowalarmlimit")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
			String thismonth=cursor.getString(cursor.getColumnIndex("thismonthwater"));
			if(thismonth!=null&&!thismonth.equals("")){
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
			}else{
				water.setThismonthwater(0.0);
			}
			water.setIsupload(cursor.getString(cursor.getColumnIndex("isupload")));
			water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
			water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
			
           
		}
		if(cursor!=null){
			cursor.close();
		}
		return water;
		
	}
	
	/**
	 * 修改本月的用水刻度
	 * 
	 * @param RFID
	 * @param thismonthwater
	 * @return
	 */
	public boolean saveWater(String waterid, Double thismonthwater
			,String desc,String isnorfid) {
		boolean b = false;
		if (waterid == null | waterid.equals("")) {
			return false;
		}
		ContentValues values = new ContentValues();
		if (thismonthwater != null) {
			values.put("thismonthwater", thismonthwater);
		}
		values.put("desc",desc);
	    values.put("thisdate", DateUtil.getBeforeDay(0));
		values.put("iscopystatus", "已复抄");
		values.put("isnorfid", "0");

		int k = db.update(DbTableName.WATERINFO, values, "_id=?",
				new String[] { waterid });
		if (k > 0) {
			System.out.println("保存成功");
			b = true;
	
		}
		return b;

	}
   
	/**
	 * 已复抄数
	 * @return
	 */
	public int queryReviewNumber(){
		int i=0;
		String sql="select _id from waterinfo where iscopystatus='已复抄' and ismultiplecopy='1'";
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			i=cursor.getCount();
		}
		if(cursor!=null){
			cursor.close();
		}
		return i;
		
	}
	
	/**
	 * 所有需要复查数
	 * @return
	 */
	public int queryAllReview(){
		int i=0;
		String sql="select _id from waterinfo where ismultiplecopy='1'";
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			i=cursor.getCount();
		}
		if(cursor!=null){
			cursor.close();
		}
		return i;
	}
	/**
	 * 查询所有未上传信息
	 * @return
	 */
	public List<WaterInfoVO> queryAllNoUpload(String waterid){
		Cursor cursor=null;
		Cursor cursorimg=null;
		 WaterInfoVO water=null;
		List<WaterInfoVO> list=new ArrayList<WaterInfoVO>();
		  String sql="select * from waterinfo where iscopystatus='已复抄' and isupload='0' and ismultiplecopy='1'";
		  if(waterid!=null){
			  sql=sql+" where _id="+waterid;
		  }
		  cursor=db.rawQuery(sql, null);
		  while(cursor.moveToNext()){
				water=new WaterInfoVO();
				water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
				water.setId(cursor.getString(cursor.getColumnIndex("_id")));
				water.setSid(cursor.getString(cursor.getColumnIndex("sid")));
				water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
				water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
				water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
				String sql2="select * from "+DbTableName.RESOURCES+" where otherid='"
				             +water.getId()+"' and type='1' and status='3'";
				cursorimg=db.rawQuery(sql2, null);
			
				List<String> imgpath=new ArrayList<String>();
				
				while(cursorimg.moveToNext()){
					
					imgpath.add(cursorimg.getString(cursorimg.getColumnIndex("path")));
				}
				water.setImagepath(imgpath);
				list.add(water);
		  }
		  if(cursor!=null){
			  cursor.close();
		  }
		  if(cursorimg!=null){
			  cursorimg.close();
		  }
		return list;
		
	};
	
	
	/**
	 * 所有已抄未上传复查数据
	 * @return
	 */
	public int queryNoUpload(){
		int i=0;
		String sql="select _id from waterinfo where ismultiplecopy='1' and iscopystatus='已复抄' and isupload='0'";
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			i=cursor.getCount();
		}
		if(cursor!=null){
			cursor.close();
		}
		return i;
	}
	
	/**
	 * 删除复抄数据
	 * @return
	 */
	public void delectReivew(){
		db.delete(DbTableName.WATERINFO, "ismultiplecopy=?", new String[]{"1"});
		db.delete(DbTableName.RESOURCES, "status=?", new String[]{"3"});
		db.delete(DbTableName.USERINFO, "ismultiplecopy=?", new String[]{"1"});
		db.delete(DbTableName.USEWATERINFO, "ismultiplecopy=?", new String[]{"1"});		
	}
	
	/**
	 * 查询复查数据
	 */
	public List<UserWaterVO> queryReviewlist(String username, String usernumber,
			String waterproperties, String wateradds, String waternumber,
			String copystatus) {
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='1'");
		sb.append(" and u.[ismultiplecopy]='1'");
	
		if(username!=null&&!username.trim().equals("")){
		
			sb.append(" and u.[username]='").append(username).append("'");
		}
		if(usernumber!=null&&!usernumber.trim().equals("")){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(waterproperties!=null&&!waterproperties.trim().equals("")){
			sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
		}
		if(wateradds!=null&&!wateradds.trim().equals("")){
			sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
		}
		if(waternumber!=null&&!waternumber.trim().equals("")){
			sb.append(" and w.[waternumber]='").append(waternumber).append("'");
		}
		if(copystatus!=null&&!copystatus.trim().equals("")){
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		String sql=sb.toString();
		System.out.println(sql);
		List<UserWaterVO> list=new ArrayList<UserWaterVO>();
		Cursor cursor=db.rawQuery(sql, null);
		while(cursor.moveToNext()){
			UserWaterVO water=new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
			String iscopy=cursor.getString(cursor.getColumnIndex("iscopystatus"));
			if(iscopy.equals("已复查")){
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
			}else{
				water.setThismonthwater(0.0);
			}		
			water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			list.add(water);			
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;
	}
	
	/**
	 * 设置成二次抄表
	 */
	
	public boolean Modifytowcopy(String number) {
		ContentValues value=new ContentValues();
		value.put("iscopystatus", "二次复抄");
	   int id= db.update(DbTableName.WATERINFO, value, "waternumber=? and ismultiplecopy=?", new String[]{number,"1"});
	   if(id>0){
		   
		   return true;
	   }
		return false;
	}
	/**
	 * 查询所有示上传的复查数据
	 * @param waterid
	 * @return
	 */
	public List<WaterInfoVO> queryReviewNoUpload(String waterid){
		
		return null;
		
	}
	/**
	 * 查询所有未复抄的数据
	 * @return
	 */
	public int queryNoCopyWater(){
		int i=0;
		String sql="select _id from waterinfo where iscopystatus='未复抄' or iscopystatus='二次复抄' and ismultiplecopy='1' ";
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			i=cursor.getInt(cursor.getColumnIndex("_id"));
		}
		if(cursor!=null){
			cursor.close();
		}
		return i;
	}
	
	/**
	 * 通过水表编号查询未上传水表信息
	 * @param waternumber
	 * @return
	 */
	public WaterInfoVO queryOneNoUpload(String waterid){
		if(waterid==null||waterid.equals("")){
			return null;
		}
		String sql="select * from waterinfo where iscopystatus='已复抄' and isupload='0' and ismultiplecopy='1'";
		
		
			sql=sql+" and _id="+waterid;
		
		Cursor cursor=db.rawQuery(sql, null);
	   WaterInfoVO water=null;
		if(cursor.moveToNext()){
			water=new WaterInfoVO();
			water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
			water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
		    water.setSid(cursor.getString(cursor.getColumnIndex("sid")));
			water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			String lastwater=cursor.getString(cursor.getColumnIndex("lastmonthwater"));
			water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
			if(!TextUtils.isEmpty(lastwater)){
				water.setLastmonthwater(Double.parseDouble(lastwater));
			}else{
				water.setLastmonthwater(0.0);
			}
			water.setIsNorfid(cursor.getString(cursor.getColumnIndex("isnorfid")));
			water.setId(cursor.getString(cursor.getColumnIndex("_id")));
		}
		
		
		if(water!=null){
			String sqlimgname="select * from  "+DbTableName.RESOURCES+" where otherid='"+
		                       water.getId()+"' and type='1' and status='3'";
			
		        Cursor c=db.rawQuery(sqlimgname, null);
		      List<String> imgpath=new ArrayList<String>();
		        while(c.moveToNext()){
		        	
		           imgpath.add(c.getString(c.getColumnIndex("path")));
		        }
		        if(c!=null){
		        	c.close();
		        }
		        if(cursor!=null){
					cursor.close();
				}
		        water.setImagepath(imgpath);
		}
		return water;
		
	}
	/**
	 * 分页查询水表信息
	 * @param model
	 * @param username
	 * @param usernumber
	 * @param waterproperties
	 * @param wateradds
	 * @param bandingstatus
	 * @param copystatu
	 * @return
	 */
	public List<UserWaterVO> queryWaterlist(Model model, String usernumber,
			String waterproperties, String wateradds, String copystatus){
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid,w.[waternumber],w.[latitude],w.[waterproperties],w.[lastmonthwater],w.[lastdate], ")
		.append("w.[iscopystatus],w.[thismonthwater],w.[longitude],w.[wateradds],u.[username],u.[usernumber]")
		.append(" from waterinfo w left join userinfo u on w.[usernumber]=u.[usernumber] where w.[ismultiplecopy]='1'");
		sb.append(" and u.[ismultiplecopy]='1'  ");
	
	
		if(usernumber!=null&&!usernumber.trim().equals("")){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(waterproperties!=null&&!waterproperties.trim().equals("")){
			sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
		}
		if(wateradds!=null&&!wateradds.trim().equals("")){
			sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
		}
		
		if(copystatus!=null&&!copystatus.trim().equals("")){
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		sb.append(" group by waterid ");
		String sql=sb.toString();
		System.out.println(sql+"复抄SQL");
		Cursor cursor=SqlUtil.getAllItems(db, (model.getIndex()-1)*model.getView_Count(), model.getView_Count(), sql);
		List<UserWaterVO> list=new ArrayList<UserWaterVO>();
		while(cursor.moveToNext()){           
			UserWaterVO water=new UserWaterVO();
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
			water.setWateradds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
			water.setLastdate(cursor.getString(cursor.getColumnIndex("lastdate")));
			String iscopy=cursor.getString(cursor.getColumnIndex("iscopystatus"));
			if(iscopy.equals("已复抄")){
				water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
			}else{
				water.setThismonthwater(0.0);
			}		
			water.setCopystatus(cursor.getString(cursor.getColumnIndex("iscopystatus")));
			water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
			water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
			water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			list.add(water);		
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;
		
	}
	
	/**
	 * 得到总页数
	 * @param username
	 * @param usernumber
	 * @param waterproperties
	 * @param wateradds
	 * @param bandingstatus
	 * @param copystatus
	 * @return
	 */
	public int queryTotalPage( String usernumber,
			String waterproperties, String wateradds, 
			String copystatus){
		int page=0;
		StringBuffer sb=new StringBuffer();
		sb.append("select w.[_id] as waterid")
		.append(" from waterinfo w,userinfo u where w.[usernumber]=u.[usernumber] and w.[ismultiplecopy]='1'");
		sb.append(" and u.[ismultiplecopy]='1' ");
	
		
		if(usernumber!=null&&!usernumber.trim().equals("")){
			sb.append(" and u.[usernumber]='").append(usernumber).append("'");
		}
		if(waterproperties!=null&&!waterproperties.trim().equals("")){
			sb.append(" and w.[waterproperties]='").append(waterproperties).append("'");
		}
		if(wateradds!=null&&!wateradds.trim().equals("")){
			sb.append(" and w.[wateradds] like'%").append(wateradds).append("%'");
		}
		
		if(copystatus!=null&&!copystatus.trim().equals("")){
			sb.append(" and w.[iscopystatus]='").append(copystatus).append("'");
		}
		
		sb.append(" group by waterid ");
		String sql=sb.toString();
		
		System.out.println(sql);
		int k=super.getCount(sql);
		page=k%10>0?k/10+1:k/10;
		return page;
		
	}
	
	/**
	 * 查询复抄是否拍照
	 * @param waterid
	 * @return
	 */
	public boolean isPhotograph(String waterid){
		if(waterid==null||waterid.equals("")){
			return false;
		}
		String sql="select * from "+DbTableName.RESOURCES+"  where status='3' and type='1' and otherid='"+waterid+"'";
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			return true;
		}
		return false;
		
	}
}
